# from pyhive import hive
from impala.dbapi import connect

class HiveUtils:
    def __init__(self, host, port, database,auth_mechanism):
        self.conn = connect(
            host=host,
            port=port,
            database=database,
            auth_mechanism = auth_mechanism
        )
        self.cursor = self.conn.cursor()

    def sum_sales_by_year(self, table_name):
        query = f"""
              SELECT SUBSTR(date_str, 1, 6) AS year, SUM(sales) AS total_sales
              FROM {table_name}
              GROUP BY SUBSTR(date_str, 1, 6)
              ORDER BY year
          """
        self.cursor.execute(query)
        result = self.cursor.fetchall()
        return result

    def get_car_city_data(self, carname,start_date):
        query = f"""
                    SELECT sales_city FROM all_sales_base_info_2  WHERE car = %s AND date_str = %s
                  """
        self.cursor.execute(query,(carname, start_date))
        result = self.cursor.fetchall()
        return result

    def sum_sales_by_automobile(self, table_name):
        query = f"""
               SELECT date_str, SUM(sales) AS total_sales,automobile
              FROM {table_name}
              GROUP BY automobile,date_str
                ORDER BY date_str
          """
        self.cursor.execute(query)
        result = self.cursor.fetchall()
        return result
    def execute_query(self, query):
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def insert_data(self, table, data):
        columns = ', '.join(data.keys())
        values = ', '.join(map(str, data.values()))
        query = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        self.cursor.execute(query)

    def update_data(self, table, set_values, condition):
        set_clause = ', '.join([f"{key}={value}" for key, value in set_values.items()])
        query = f"UPDATE {table} SET {set_clause} WHERE {condition}"
        self.cursor.execute(query)

    def delete_data(self, table, condition):
        query = f"DELETE FROM {table} WHERE {condition}"
        self.cursor.execute(query)

    def close_connection(self):
        self.cursor.close()
        self.conn.close()